package ws.jonas.hoarder.db;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import ws.jonas.common.config.Config;
import ws.jonas.common.log.Logger;

public class MysqlDb extends SqlDb {
	protected static final Logger log = Logger.getInstance(MysqlDb.class);
	
	private static final String SELECT_PRODUCT_ID = "SELECT id FROM products WHERE prod_nr = ? AND volume = ?";
	private static final String SELECT_STORE_ID = "SELECT id FROM stores WHERE city = ? AND address = ? AND county = ?";
	private static final String INSERT_PRODUCT = "INSERT INTO products(prod_nr, volume, name) VALUES(?, ?, ?)";
	private static final String INSERT_STORE = "INSERT INTO stores(city, address, county) VALUES(?, ?, ?)";
	private static final String INSERT_STOCK = "INSERT INTO stocks(prod_id, store_id, quantity, insert_timestamp)" +
			"VALUES((SELECT id FROM products WHERE prod_nr = ? AND volume = ?), (SELECT id FROM stores WHERE city = ? AND address = ? and county = ?), ?, NOW())";
	
	public MysqlDb() throws SQLException {
		Properties props = new Properties();
		props.put("user", Config.get(Config.DB_USER_KEY));
		props.put("password", Config.get(Config.DB_PASSWORD_KEY));
		props.put("characterEncoding", "UTF-8");
		try {
			conn = DriverManager.getConnection("jdbc:mysql://" + Config.get(Config.DB_KEY), props);
		} catch (SQLException e) {
			log.severe("Could not set up database connection, shutting down: " + e.getMessage());
			throw(e);
		}

		try {
			selectProductIdPs = conn.prepareStatement(SELECT_PRODUCT_ID);
			selectStoreIdPs = conn.prepareStatement(SELECT_STORE_ID);
			insertProductPs = conn.prepareStatement(INSERT_PRODUCT);
			insertStorePs = conn.prepareStatement(INSERT_STORE);
			insertStockPs = conn.prepareStatement(INSERT_STOCK);
		} catch (SQLException e) {
			log.severe("Could not set up database queries, shutting down");
			log.severe(e);
			throw(e);
		}
	}
}
